const db = require('../db/connMysql');
const mysql_tool = require('../util/mysql_tool');
const minio = require('../db/connMinio');

const interviewDao = {
    /*
     * 查询自己学院的所有访谈记录
     */
    async getAllInterviewListOfSelfFaculty(uid) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql1 = `select
                                CONCAT('r', CAST(i.interviewId as CHAR)) as interviewId,
                                s.cardId as studentCardId,
                                s.name as studentName,
                                f.facultyId as facultyId,
                                f.name as facultyName,
                                c.name as className,
                                '日常访谈' as interviewType,
                                i.interviewContent as interviewContent,
                                u.uid as interviewTeacherId,
                                u.username as interviewTeacher,
                                CASE
                                    WHEN i.isWarning = 'true' then '是'
                                    ELSE '否'
                                END as warningStatus,
                                DATE_FORMAT(i.startTime, '%Y-%m-%d %H:%i:%s') as interviewTime
                            from DailyVisitInterviewRecords as i
                            left join Students as s on s.studentId = i.studentId
                            left join Classes as c on c.classId = s.classId
                            left join Facultys as f on f.facultyId = c.facultyId
                            left join Users as u on u.uid = i.teacherId
                            where f.facultyId = (select facultyId from Users where uid = ?)`;
                let sql2 = `select
                                CONCAT('p', CAST(i.interviewId as CHAR)) as interviewId,
                                s.cardId as studentCardId,
                                s.name as studentName,
                                f.facultyId as facultyId,
                                f.name as facultyName,
                                c.name as className,
                                '普查访谈' as interviewType,
                                CONCAT(
                                    '1. 最近情绪怎么样？', question1,
                                    '2. 最近的学习、生活怎样？有什么困难吗？', question2,
                                    '3. 人际关系  有好朋友吗？都是谁？舍友相处的怎样？和谁关系近？', question3,
                                    '4. 对学校的喜好及适应', question4,
                                    '5. 家庭情况及成长史', question5,
                                    '6. 睡眠、身体、精神状况等。是否做过心理咨询？', question6,
                                    '7. 是否有自杀的想法？频次、时间等', question7
                                ) as interviewContent,
                                u.uid as interviewTeacherId,
                                u.username as interviewTeacher,
                                w.name as warningStatus,
                                DATE_FORMAT(i.startTime, '%Y-%m-%d %H:%i:%s') as interviewTime
                            from CensusVisitInterviewRecords as i
                            left join Students as s on s.studentId = i.studentId
                            left join Classes as c on c.classId = s.classId
                            left join Facultys as f on f.facultyId = c.facultyId
                            left join Users as u on u.uid = i.counselorId
                            left join WarningLevels as w on w.warningLevelId = i.warningLevelId
                            where f.facultyId = (select facultyId from Users where uid = ?)`;
                let sql3 = `select
                            CONCAT('j', CAST(i.interviewId as CHAR)) as interviewId,
                            s.cardId as studentCardId,
                            s.name as studentName,
                            f.facultyId as facultyId,
                            f.name as facultyName,
                            c.name as className,
                            '家校沟通' as interviewType,
                            i.interviewContent as interviewContent,
                            u.uid as interviewTeacherId,
                            u.username as interviewTeacher,
                            CASE
                                WHEN i.isWarning = 'true' then '是'
                                ELSE '否'
                            END as warningStatus,
                            DATE_FORMAT(i.startTime, '%Y-%m-%d %H:%i:%s') as interviewTime
                        from HomeSchoolInterviewRecords as i
                        left join Students as s on s.studentId = i.studentId
                        left join Classes as c on c.classId = s.classId
                        left join Facultys as f on f.facultyId = c.facultyId
                        left join Users as u on u.uid = i.teacherId
                        where f.facultyId = (select facultyId from Users where uid = ?)`;
                let sql = '(' + sql1 + ') UNION (' + sql2 + ') UNION (' + sql3 + ')';
                // let sql = '(' + sql1 + ') UNION (' + sql2 + ')';
                // let sql = `(${sql1}) AS DerivedTable1 UNION (${sql2}) AS DerivedTable2`;
                // let sql = `select * from (${sql3})`;
                // let sql = `select * from (${sql3}) as CombinedResults where facultyId = (select facultyId from Users where uid = ?)`;
                connection.query(sql, [uid, uid, uid], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 查询所有访谈记录
     */
    async getAllInterviewList() {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql1 = `select
                                CONCAT('r', CAST(i.interviewId as CHAR)) as interviewId,
                                s.cardId as studentCardId,
                                s.name as studentName,
                                f.facultyId as facultyId,
                                f.name as facultyName,
                                c.name as className,
                                '日常访谈' as interviewType,
                                i.interviewContent as interviewContent,
                                u.uid as interviewTeacherId,
                                u.username as interviewTeacher,
                                CASE
                                    WHEN i.isWarning = 'true' then '是'
                                    ELSE '否'
                                END as warningStatus,
                                DATE_FORMAT(i.startTime, '%Y-%m-%d %H:%i:%s') as interviewTime
                            from DailyVisitInterviewRecords as i
                            left join Students as s on s.studentId = i.studentId
                            left join Classes as c on c.classId = s.classId
                            left join Facultys as f on f.facultyId = c.facultyId
                            left join Users as u on u.uid = i.teacherId`;
                let sql2 = `select
                                CONCAT('p', CAST(i.interviewId as CHAR)) as interviewId,
                                s.cardId as studentCardId,
                                s.name as studentName,
                                f.facultyId as facultyId,
                                f.name as facultyName,
                                c.name as className,
                                '普查访谈' as interviewType,
                                CONCAT(
                                    '1. 最近情绪怎么样？', question1,
                                    '2. 最近的学习、生活怎样？有什么困难吗？', question2,
                                    '3. 人际关系  有好朋友吗？都是谁？舍友相处的怎样？和谁关系近？', question3,
                                    '4. 对学校的喜好及适应', question4,
                                    '5. 家庭情况及成长史', question5,
                                    '6. 睡眠、身体、精神状况等。是否做过心理咨询？', question6,
                                    '7. 是否有自杀的想法？频次、时间等', question7
                                ) as interviewContent,
                                u.uid as interviewTeacherId,
                                u.username as interviewTeacher,
                                w.name as warningStatus,
                                DATE_FORMAT(i.startTime, '%Y-%m-%d %H:%i:%s') as interviewTime
                            from CensusVisitInterviewRecords as i
                            left join Students as s on s.studentId = i.studentId
                            left join Classes as c on c.classId = s.classId
                            left join Facultys as f on f.facultyId = c.facultyId
                            left join Users as u on u.uid = i.counselorId
                            left join WarningLevels as w on w.warningLevelId = i.warningLevelId`;
                let sql3 = `select
                                CONCAT('j', CAST(i.interviewId as CHAR)) as interviewId,
                                s.cardId as studentCardId,
                                s.name as studentName,
                                f.facultyId as facultyId,
                                f.name as facultyName,
                                c.name as className,
                                '家校沟通' as interviewType,
                                i.interviewContent as interviewContent,
                                u.uid as interviewTeacherId,
                                u.username as interviewTeacher,
                                CASE
                                    WHEN i.isWarning = 'true' then '是'
                                    ELSE '否'
                                END as warningStatus,
                                DATE_FORMAT(i.startTime, '%Y-%m-%d %H:%i:%s') as interviewTime
                            from HomeSchoolInterviewRecords as i
                            left join Students as s on s.studentId = i.studentId
                            left join Classes as c on c.classId = s.classId
                            left join Facultys as f on f.facultyId = c.facultyId
                            left join Users as u on u.uid = i.teacherId`;
                let sql = '(' + sql1 + ') UNION (' + sql2 + ') UNION (' + sql3 + ')';
                connection.query(sql, [], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 新增日常访谈记录
     */
    async addDailyVisitInterview(studentId, startTime, endTime, teacherId, interviewContent, isWarning, interviewPhoto) {
        let isRollback = false;
        let result = await new Promise((resolve, reject) => {
            db.getConnection(async (err, connection) => {
                try {
                    if (err) {
                        // 连不上数据库
                        connection.release();
                        reject(err);
                        return ;
                    }
    
                    // 开启事务
                    let start_transaction = await mysql_tool.mysqlTransation(connection);
                    if (start_transaction.success == false) {
                        reject(start_transaction.err);
                        return ;
                    }
    
                    // 新增一行日常访谈的记录
                    let sql1 = `insert into DailyVisitInterviewRecords(
                                   studentId, startTime, endTime, teacherId, interviewContent, isWarning, interviewPhoto
                               ) values(
                                   ?, ?, ?, ?, ?, ?, ""
                               )`;
                    let data1 = [studentId, startTime, endTime, teacherId, interviewContent, isWarning];
                    let dailyVisitInterview = await mysql_tool.execSqlInTransaction(connection, sql1, data1);
                    if (dailyVisitInterview.success == false) {
                        reject(dailyVisitInterview.err);
                        return ;
                    }
                    dailyVisitInterview = dailyVisitInterview.result;
                    let insertId = dailyVisitInterview.insertId;       // 日常访谈记录的id
    
                    // 提取 base64 数据部分
                    let base64Data = interviewPhoto.split(',')[1];
                    // 将 base64 数据转换为 Buffer
                    let buffer = Buffer.from(base64Data, 'base64');
                    // 定义 MinIO 的 bucket 名称和对象名称
                    let bucketName = 'public'; // 替换为你的 bucket 名称
                    let objectName = `DailyVisitInterviewRecords-${insertId}.png`; // 替换为你希望在 MinIO 中存储的对象名称

                    // 上传 Buffer 到 MinIO
                    await minio.minio.putObject(bucketName, objectName, buffer, buffer.length, async function (err, etag) {
                        if (err) {
                            await mysql_tool.rollbackTransaction(connection);
                            isRollback = true;
                            reject(err);
                            return ;
                        }
                    });

                    // 插入到 publicimg中
                    let sql2 = `INSERT INTO PublicImg(
                            browser, uri, status
                        ) VALUES (?, ?, ?)`;
                    let data2 = [bucketName, objectName, '已完成'];
                    let result2 = await mysql_tool.execSqlInTransaction(connection, sql2, data2);
                    let imgId = result2.result.insertId;
    
                    // 更新imgId到病例档案中
                    let sql3 = `UPDATE DailyVisitInterviewRecords SET imgId = ? WHERE interviewId = ?`;
                    let data3 = [imgId, insertId];
                    await mysql_tool.execSqlInTransaction(connection, sql3, data3);

                    // 提交事务
                    if (!isRollback) {
                        // 返回结果
                        await mysql_tool.commitTransaction(connection);
                        resolve(dailyVisitInterview);
                    }
                } catch (err) {
                    console.log('【上传图片接口报错】', err);
                    reject(err);
                }
            });
        });
        return result;
    },
    async addDailyVisitInterviewNoImg(studentId, startTime, endTime, teacherId, interviewContent, isWarning) {
        // 新增一行日常访谈的记录
        let sql = `insert into DailyVisitInterviewRecords(
            studentId, startTime, endTime, teacherId, interviewContent, isWarning, interviewPhoto
        ) values(
            ?, ?, ?, ?, ?, ?, ""
        )`;
        let data = [studentId, startTime, endTime, teacherId, interviewContent, isWarning];
        return mysql_tool.execSql(sql, data);
    },
    /*
     * 新增普查访谈记录
     */
    async addCensusVisitInterview(studentId, startTime, endTime, counselorId, question1, question2, question3, question4, question5, question6, question7, note, centerOpinionId, warningLevelId) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection(async (err, connection) => {
                try {
                    if (err) {
                        // 连不上数据库
                        connection.release();
                        reject(err);
                        return ;
                    }
    
                    // 开启事务
                    let start_transaction = await mysql_tool.mysqlTransation(connection);
                    if (start_transaction.success == false) {
                        reject(start_transaction.err);
                        return ;
                    }
    
                    // 新增一行普查访谈的记录
                    let sql = `insert into CensusVisitInterviewRecords(
                                   studentId, startTime, endTime, counselorId, question1, question2, question3, question4, question5, question6, question7, note, centerOpinionId, warningLevelId
                               ) values(
                                   ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
                               )`;
                    let data = [studentId, startTime, endTime, counselorId, question1, question2, question3, question4, question5, question6, question7, note, centerOpinionId, warningLevelId];
                    let interview = await mysql_tool.execSqlInTransaction(connection, sql, data);
                    if (interview.success == false) {
                        reject(interview.err);
                        return ;
                    }
                    interview = interview.result;
    
                    // 提交事务
                    await mysql_tool.commitTransaction(connection);
    
                    // 返回结果
                    resolve(interview);
                } catch (err) {
                    console.log('【上传图片接口报错】', err);
                    reject(err);
                }
            });
        });
        return result;
    },
    /*
     * 新增家校沟通访谈记录
     */
    async addHomeSchoolInterview(studentId, startTime, endTime, teacherId, interviewContent, isWarning, interviewPhoto) {
        let isRollback = false;
        let result = await new Promise((resolve, reject) => {
            db.getConnection(async (err, connection) => {
                try {
                    if (err) {
                        // 连不上数据库
                        connection.release();
                        reject(err);
                        return ;
                    }
    
                    // 开启事务
                    let start_transaction = await mysql_tool.mysqlTransation(connection);
                    if (start_transaction.success == false) {
                        reject(start_transaction.err);
                        return ;
                    }
    
                    let sql = `insert into HomeSchoolInterviewRecords(
                                   studentId, startTime, endTime, teacherId, interviewContent, isWarning, interviewPhoto
                               ) values(
                                   ?, ?, ?, ?, ?, ?, ""
                               )`;
                    let data = [studentId, startTime, endTime, teacherId, interviewContent, isWarning];
                    let interview = await mysql_tool.execSqlInTransaction(connection, sql, data);
                    if (interview.success == false) {
                        reject(interview.err);
                        return ;
                    }
                    interview = interview.result;
                    let insertId = interview.insertId;       // 家校访谈记录的id
    
                    // 提取 base64 数据部分
                    let base64Data = interviewPhoto.split(',')[1];
                    // 将 base64 数据转换为 Buffer
                    let buffer = Buffer.from(base64Data, 'base64');
                    // 定义 MinIO 的 bucket 名称和对象名称
                    let bucketName = 'public'; // 替换为你的 bucket 名称
                    let objectName = `HomeSchoolInterviewRecords-${insertId}.png`; // 替换为你希望在 MinIO 中存储的对象名称

                    // 上传 Buffer 到 MinIO
                    await minio.minio.putObject(bucketName, objectName, buffer, buffer.length, async function (err, etag) {
                        if (err) {
                            await mysql_tool.rollbackTransaction(connection);
                            isRollback = true;
                            reject(err);
                            return ;
                        }
                    });

                    // 插入到 publicimg中
                    let sql2 = `INSERT INTO PublicImg(
                            browser, uri, status
                        ) VALUES (?, ?, ?)`;
                    let data2 = [bucketName, objectName, '已完成'];
                    let result2 = await mysql_tool.execSqlInTransaction(connection, sql2, data2);
                    let imgId = result2.result.insertId;
    
                    // 更新imgId到病例档案中
                    let sql3 = `UPDATE HomeSchoolInterviewRecords SET imgId = ? WHERE interviewId = ?`;
                    let data3 = [imgId, insertId];
                    await mysql_tool.execSqlInTransaction(connection, sql3, data3);

                    // 提交事务
                    if (!isRollback) {
                        // 返回结果
                        await mysql_tool.commitTransaction(connection);
                        resolve(interview);
                    }
                } catch (err) {
                    console.log('【上传图片接口报错】', err);
                    reject(err);
                }
            });
        });
        return result;
    },
    async addHomeSchoolInterviewNoImg(studentId, startTime, endTime, teacherId, interviewContent, isWarning) {
        let sql = `insert into HomeSchoolInterviewRecords(
            studentId, startTime, endTime, teacherId, interviewContent, isWarning, interviewPhoto
        ) values(
            ?, ?, ?, ?, ?, ?, ""
        )`;
        let data = [studentId, startTime, endTime, teacherId, interviewContent, isWarning];
        return mysql_tool.execSql(sql, data);
    },
    /*
     * 修改日常访谈记录
     */
    async updateDailyInterview(interviewId, startTime, teacherId, interviewContent, isWarning) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = 'update DailyVisitInterviewRecords set startTime=?, teacherId=?, interviewContent=?, isWarning=? where interviewId=?';
                let data = [startTime, teacherId, interviewContent, isWarning, interviewId];
                connection.query(sql, data, function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 修改普查访谈记录
     */
    async updateCensusInterview(interviewId, startTime, counselorId, question1, question2, question3, question4, question5, question6, question7, note, centerOpinionId, warningLevelId) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = 'update CensusVisitInterviewRecords set startTime=?, counselorId=?, question1=?, question2=?, question3=?, question4=?, question5=?, question6=?, question7=?, note=?, centerOpinionId=?, warningLevelId=? where interviewId=?';
                let data = [startTime, counselorId, question1, question2, question3, question4, question5, question6, question7, note, centerOpinionId, warningLevelId, interviewId];
                connection.query(sql, data, function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 修改家校沟通访谈记录接口
     */
    async updateHomeSchoolInterview(interviewId, startTime, teacherId, interviewContent, isWarning) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = 'update HomeSchoolInterviewRecords set startTime=?, teacherId=?, interviewContent=?, isWarning=? where interviewId=?';
                let data = [startTime, teacherId, interviewContent, isWarning, interviewId];
                connection.query(sql, data, function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 删除某个日常访谈
     */
    async deleteDailyInterview(interviewId) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                // 删除记录
                let sql = `delete from DailyVisitInterviewRecords where interviewId = ?`;
                connection.query(sql, [interviewId], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 删除普查访谈记录接口
     */
    async deleteCensusInterview(interviewId) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                // 删除记录
                let sql = `delete from CensusVisitInterviewRecords where interviewId = ?`;
                connection.query(sql, [interviewId], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 删除家校沟通访谈记录接口
     */
    async deleteHomeSchoolInterview(interviewId) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                // 删除记录
                let sql = `delete from HomeSchoolInterviewRecords where interviewId = ?`;
                connection.query(sql, [interviewId], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 查询某个日常访谈的详细信息
     */
    async getDailyInterviewInfo(interviewId) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `select
                               s.cardId as cardId,
                               s.name as studentName,
                               c.name as className,
                               f.name as facultyName,
                               c.major as major,
                               DATE_FORMAT(d.startTime, '%Y-%m-%d %H:%i:%s') as startTime,
                               DATE_FORMAT(d.endTime, '%Y-%m-%d %H:%i:%s') as endTime,
                               Users.username as teacher,
                               d.interviewContent as interviewContent,
                               d.isWarning as isWarning,
                               d.imgId as imgId
                           from DailyVisitInterviewRecords as d
                           left join Users on d.teacherId = Users.uid
                           left join Students as s on d.studentId = s.studentId
                           left join Classes as c on c.classId = s.classId
                           left join Facultys as f on f.facultyId = c.facultyId
                           where d.interviewId=?`;
                connection.query(sql, [interviewId], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 查询某个普查访谈的详细信息
     */
    async getCensusInterviewInfo(interviewId) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `select
                               s.cardId as cardId,
                               s.name as studentName,
                               c.name as className,
                               f.name as facultyName,
                               c.major as major,
                               DATE_FORMAT(ci.startTime, '%Y-%m-%d %H:%i:%s') as startTime,
                               DATE_FORMAT(ci.endTime, '%Y-%m-%d %H:%i:%s') as endTime,
                               u.username as counselor,
                               ci.question1 as question1,
                               ci.question2 as question2,
                               ci.question3 as question3,
                               ci.question4 as question4,
                               ci.question5 as question5,
                               ci.question6 as question6,
                               ci.question7 as question7,
                               ci.note as note,
                               co.centerOpinion as centerOpinion,
                               wl.name as warningLevel,
                               f.facultyId as facultyId,
                               ci.counselorId as counselorId,
                               co.centerOpinionId as centerOpinionId,
                               wl.warningLevelId as warningLevelId
                           from CensusVisitInterviewRecords as ci
                           left join Students as s on ci.studentId = s.studentId
                           left join Classes as c on s.classId = c.classId
                           left join Facultys as f on c.facultyId = f.facultyId
                           left join Users as u on u.uid = ci.counselorId
                           left join WarningLevels as wl on ci.warningLevelId = wl.warningLevelId
                           left join CenterOpinion as co on ci.centerOpinionId = co.centerOpinionId
                           where ci.interviewId = ?`;
                connection.query(sql, [interviewId], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 查询某个家校沟通访谈的详细信息
     */
    async getHomeSchoolInterviewInfo(interviewId) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `select
                               s.cardId as cardId,
                               s.name as studentName,
                               c.name as className,
                               f.name as facultyName,
                               c.major as major,
                               DATE_FORMAT(d.startTime, '%Y-%m-%d %H:%i:%s') as startTime,
                               DATE_FORMAT(d.endTime, '%Y-%m-%d %H:%i:%s') as endTime,
                               Users.username as teacher,
                               d.interviewContent as interviewContent,
                               d.isWarning as isWarning,
                               d.imgId as imgId
                           from HomeSchoolInterviewRecords as d
                           left join Users on d.teacherId = Users.uid
                           left join Students as s on d.studentId = s.studentId
                           left join Classes as c on c.classId = s.classId
                           left join Facultys as f on f.facultyId = c.facultyId
                           where d.interviewId=?`;
                connection.query(sql, [interviewId], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 获取操作者对应学院对应学生的所有访谈记录
     */
    async getInterviewByStudentOfSelfFaculty(uid, studentId) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql1 = `select
                               CONCAT('r', CAST(i.interviewId as CHAR)) as interviewId,
                               s.cardId as studentCardId,
                               s.name as studentName,
                               f.name as facultyName,
                               c.name as className,
                               '日常访谈' as interviewType,
                               u.username as interviewTeacher,
                               CASE
                                   WHEN i.isWarning = 'true' then '是'
                                   ELSE '否'
                               END as warningStatus,
                               DATE_FORMAT(i.startTime, '%Y-%m-%d %H:%i:%s') as interviewTime
                           from DailyVisitInterviewRecords as i
                           left join Students as s on s.studentId = i.studentId
                           left join Classes as c on c.classId = s.classId
                           left join Facultys as f on f.facultyId = c.facultyId
                           left join Users as u on u.uid = i.teacherId
                           where i.studentId = ? and f.facultyId = (select facultyId from Users where uid = ?)`;
                let sql2 = `select
                                CONCAT('p', CAST(i.interviewId as CHAR)) as interviewId,
                                s.cardId as studentCardId,
                                s.name as studentName,
                                f.name as facultyName,
                                c.name as className,
                                '普查访谈' as interviewType,
                                u.username as interviewTeacher,
                                w.name as warningStatus,
                                DATE_FORMAT(i.startTime, '%Y-%m-%d %H:%i:%s') as interviewTime
                            from CensusVisitInterviewRecords as i
                            left join Students as s on s.studentId = i.studentId
                            left join Classes as c on c.classId = s.classId
                            left join Facultys as f on f.facultyId = c.facultyId
                            left join Users as u on u.uid = i.counselorId
                            left join WarningLevels as w on w.warningLevelId = i.warningLevelId
                            where i.studentId = ? and f.facultyId = (select facultyId from Users where uid = ?)`;
                let sql3 = `select
                                CONCAT('j', CAST(i.interviewId as CHAR)) as interviewId,
                                s.cardId as studentCardId,
                                s.name as studentName,
                                f.name as facultyName,
                                c.name as className,
                                '家校沟通' as interviewType,
                                u.username as interviewTeacher,
                                CASE
                                    WHEN i.isWarning = 'true' then '是'
                                    ELSE '否'
                                END as warningStatus,
                                DATE_FORMAT(i.startTime, '%Y-%m-%d %H:%i:%s') as interviewTime
                            from HomeSchoolInterviewRecords as i
                            left join Students as s on s.studentId = i.studentId
                            left join Classes as c on c.classId = s.classId
                            left join Facultys as f on f.facultyId = c.facultyId
                            left join Users as u on u.uid = i.teacherId
                            where i.studentId = ? and f.facultyId = (select facultyId from Users where uid = ?)`;
                // let sql = '(' + sql1 + ') UNION (' + sql2 + ')';
                let sql = '(' + sql1 + ') UNION (' + sql2 + ') UNION (' + sql3 + ')';
                connection.query(sql, [studentId, uid, studentId, uid, studentId, uid], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 获取对应学生的所有访谈记录
     */
    async getInterviewByStudent(uid, studentId) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql1 = `select
                               CONCAT('r', CAST(i.interviewId as CHAR)) as interviewId,
                               s.cardId as studentCardId,
                               s.name as studentName,
                               f.name as facultyName,
                               c.name as className,
                               '日常访谈' as interviewType,
                               u.username as interviewTeacher,
                               CASE
                                   WHEN i.isWarning = 'true' then '是'
                                   ELSE '否'
                               END as warningStatus,
                               DATE_FORMAT(i.startTime, '%Y-%m-%d %H:%i:%s') as interviewTime
                           from DailyVisitInterviewRecords as i
                           left join Students as s on s.studentId = i.studentId
                           left join Classes as c on c.classId = s.classId
                           left join Facultys as f on f.facultyId = c.facultyId
                           left join Users as u on u.uid = i.teacherId
                           where i.studentId = ?`;
                let sql2 = `select
                                CONCAT('p', CAST(i.interviewId as CHAR)) as interviewId,
                                s.cardId as studentCardId,
                                s.name as studentName,
                                f.name as facultyName,
                                c.name as className,
                                '普查访谈' as interviewType,
                                u.username as interviewTeacher,
                                w.name as warningStatus,
                                DATE_FORMAT(i.startTime, '%Y-%m-%d %H:%i:%s') as interviewTime
                            from CensusVisitInterviewRecords as i
                            left join Students as s on s.studentId = i.studentId
                            left join Classes as c on c.classId = s.classId
                            left join Facultys as f on f.facultyId = c.facultyId
                            left join Users as u on u.uid = i.counselorId
                            left join WarningLevels as w on w.warningLevelId = i.warningLevelId
                            where i.studentId = ?`;
                let sql3 = `select
                                CONCAT('j', CAST(i.interviewId as CHAR)) as interviewId,
                                s.cardId as studentCardId,
                                s.name as studentName,
                                f.name as facultyName,
                                c.name as className,
                                '家校沟通' as interviewType,
                                u.username as interviewTeacher,
                                CASE
                                    WHEN i.isWarning = 'true' then '是'
                                    ELSE '否'
                                END as warningStatus,
                                DATE_FORMAT(i.startTime, '%Y-%m-%d %H:%i:%s') as interviewTime
                            from HomeSchoolInterviewRecords as i
                            left join Students as s on s.studentId = i.studentId
                            left join Classes as c on c.classId = s.classId
                            left join Facultys as f on f.facultyId = c.facultyId
                            left join Users as u on u.uid = i.teacherId
                            where i.studentId = ?`;
                // let sql = '(' + sql1 + ') UNION (' + sql2 + ')';
                let sql = '(' + sql1 + ') UNION (' + sql2 + ') UNION (' + sql3 + ')';
                connection.query(sql, [studentId, studentId, studentId], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 查询所有中心意见
     */
    async getAllCenterOpinion() {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `select * from CenterOpinion`;
                connection.query(sql, [], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    }
};

module.exports = interviewDao;